package simorg.domain.persistence;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;

import simorg.domain.ResultString;
import simorg.util.DatabaseConnection;

public class ResultStringTable {
	
	public static void bootstrap(boolean drop) throws Exception {
		DatabaseConnection dc = new DatabaseConnection();
		try {
			Statement stmt = null;
		    String query = null;
		    if ( drop ) {
		    	query = "DROP TABLE IF EXISTS RESULT_STRING";
		    	stmt = dc.getConnection().createStatement();
			    stmt.execute(query);
		    }
		    query = "CREATE TABLE IF NOT EXISTS RESULT_STRING" +
		    	" (ID INT PRIMARY KEY, TEXT VARCHAR(500))";
		    stmt = dc.getConnection().createStatement();
		    stmt.execute(query);
		}
		finally {
			dc.getConnection().close();
		}
	}
	
	
	public static void save(DatabaseConnection dc, ResultString string) {
		try {
			String query = null;
			if ( findById(dc, string.getId()) == null) {
				query = "INSERT INTO RESULT_STRING (ID, TEXT) " +
								"VALUES ( ?, ?)";
				PreparedStatement insert = dc.getConnection().prepareStatement(query);
				ResultStringTable.fillPreparedStatement(string, insert);
				insert.executeUpdate();
				ResultSet rs = insert.getGeneratedKeys();
				while (rs.next()){
					string.setId(rs.getInt(1));
				}
			}
			else {
				// update existing course
				throw new RuntimeException("Result Strings are read-only");
				/*
				query = "UPDATE RESULT_STRING SET ID=?, TEXT=? " +
						"WHERE ID=?";
				PreparedStatement update = dc.getConnection().prepareStatement(query);
				ResultStringTable.fillPreparedStatement(string, update);
				update.setInt(3, string.getId());
				update.executeUpdate();*/
			}
			
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	
	public static void fillFromResultSet(ResultString s,ResultSet rs) throws Exception {
		s.setId(rs.getInt("ID"));
		s.setText(rs.getString("TEXT"));
	}

	public static void fillPreparedStatement(ResultString string, PreparedStatement stmt) throws Exception {
		stmt.setInt(1, string.getId());
		stmt.setString(2, string.getText());
	}
	
	public static ArrayList<ResultString> find(DatabaseConnection dc, QueryConstraints constraints) {
		ArrayList<ResultString> retval = new ArrayList<ResultString>();
		try {
			String query = "SELECT * FROM RESULT_STRING " + constraints.getParameterString();
			PreparedStatement stmt = dc.getConnection().prepareStatement(query);
			constraints.fillPreparedStatement(stmt);
			ResultSet rs = stmt.executeQuery();
			while (rs.next()){
				ResultString string = new ResultString();
				ResultStringTable.fillFromResultSet(string, rs);
		        retval.add(string);
		    }
		} catch (Exception e) {
			e.printStackTrace();
		}
		return retval;
	}

	
	public static ResultString findById(DatabaseConnection db, int id) {
		QueryConstraints constraints = new QueryConstraints();
		constraints.getConstraints().add(new QueryConstraint("ID", id));
		ArrayList<ResultString> result = find(db, constraints);
		return result.size() > 0 ? result.get(0) : null;
	}
}
